Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# MAINTAIN

The MAINTAIN command enters and maintains the values of dimensions, composites, and partition template objects.

Note: You can also issue a MAINTAIN statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN object {ADD|DELETE|RENAME|MOVE|MERGE} args

The keywords that you can use with the MAINTAIN command varies by object:

- MAINTAIN dimension {ADD|DELETE|RENAME|MOVE|MERGE} args

  The keyword that you can use varies by the type of dimension that you want to maintain:

  - With a non-concat dimension, you can use the ADD, DELETE, RENAME, MOVE, or MERGE keywords to add, delete, rename, move, or merge non-concat dimension values. You can also use the ADD keyword to add temporary calculated members to a dimension.

  - With a concat dimension, you can only use the MOVE keyword to move concat dimension values.

- MAINTAIN composite {ADD|DELETE|MERGE} args

- MAINTAIN partition-template {ADD|DELETE|MOVE} args

The specific syntax varies by keyword. Consequently, there are separate topics for each keyword of the MAINTAIN command:

  MAINTAIN ADD
  MAINTAIN DELETE
  MAINTAIN MERGE
  MAINTAIN MOVE
  MAINTAIN RENAME

For information that applies to the MAINTAIN command in general, see the Notes in this topic.

Notes

Triggering Program Execution When MAINTAIN Executes

Using the TRIGGER command, you can make the execution of a MAINTAIN statement an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information.

Automatic Status Reset

When you use the ADD, DELETE, MERGE, or MOVE keyword to maintain a dimension or composite whose status is not currently ALL, the MAINTAIN command automatically resets status to ALL before performing the maintenance function. However, when you use the RENAME keyword to maintain a dimension whose status is not currently ALL, the MAINTAIN command does not change the status of the dimension.

Maintain Permission

You cannot perform maintenance on a dimension when a PERMIT MAINTAIN statement denies maintain permission for the dimension. Maintain permission is implicitly denied whenever read permission is restricted for a dimension, even when you specify maintain permission for the dimension. (See PERMIT.)

Maintaining Dimensions in Multiwriter Analytic Workspaces

Keep the following points in mind when maintaining dimensions in an analytic workspace that is attached in multiwriter mode:

- You cannot update a variable when any of its dimensions have been acquired and modified.

- Reverting a dimension after adding dimension values is not recommended since it can result in suboptimal space allocation for variables dimensioned by the dimension.

- When an acquired variable is dimensioned by an acquired dimension that has been maintained, you cannot update the variable until the dimension is updated or released.

- You do not need to acquire composites in order for them to be maintained, Oracle OLAP automatically performs concurrent dimension maintenance for the composite dimensions.

Also, before you can maintain dimensions in an analytic workspace that is attached in multiwriter mode, you must first acquire the dimension using an ACQUIRE statement.

For example, assume that user A and user B both need to perform what-if computations on both `actuals` and `budget`. After performing the what-if computations, user A needs to modify `actuals` and B needs to modify `budget`. Finally, both user A and user B need to add a new `time` dimension value and add data corresponding to that new dimension value to `actuals` or `budget`.

User A issues the following OLAP DML statements.

    AW ATTACH myworkspace MULTI
    ...make modifications
    ACQUIRE actuals
    ...make more modifications
    ACQUIRE time
    MAINTAIN time ADD 'Y2002'
    actuals (time 'Y2002', ...) = ...
    UPDATE MULTI actuals, time
    COMMIT
    RELEASE actuals, time
    AW DETACH myworkspace

User B issues the following OLAP DML statements.

    AW ATTACH myworkspace MULTI
    ...make modifications
    ACQUIRE budget
    ...make more modifications
    ACQUIRE time--> failed
    ACQUIRE RESYNC time WAIT
    MAINTAIN time ADD 'Y2003'
    budget (time 'Y2003', ...) = ...
    UPDATE MULTI budget, time
    COMMIT
    RELEASE budget, time
    AW DETACH myworkspace

MAINTAIN and Dimension Surrogates

You cannot use a MAINTAIN statement on a dimension surrogate. You can only use MAINTAIN to add values to or delete them from a dimension. However, when you add or delete a dimension value, then Oracle OLAP adds or removes a position from surrogates of that dimension. When you add a position to a dimension, the corresponding position in a surrogate for that dimension receives an `NA` value.

Maintaining a Concat Dimension

A concat dimension contains the values of its component dimensions. You do not directly add, merge, or delete the values of a concat dimension using MAINTAIN statements. Instead, when you add, merge, or delete values from a component dimension of the concat, Oracle OLAP automatically adds or deletes the values from the concat dimension. You can use the MOVE keyword of the MAINTAIN command to change the order of the values of a concat dimension.

## MAINTAIN ADD

The MAINTAIN command with the ADD keyword adds new TEXT, ID, and INTEGER values to a non-concat dimension, composite, or partition; or adds a new temporary calculated member to a dimension.

Note: You can also issue a MAINTAIN ADD for TEXT, ID, and INTEGER Values statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program one time for each value; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

The syntax for using the MAINTAIN command with the ADD keyword depends on the type of the object being maintained and whether you are adding a permanent or temporary member.

For this reason, the following separate entries are provided for MAINTAIN ADD:

- MAINTAIN ADD for TEXT, ID, and INTEGER Values

- MAINTAIN ADD for DAY, WEEK, MONTH, QUARTER, and YEAR Values

- MAINTAIN ADD SESSION

- MAINTAIN ADD TO PARTITION

### MAINTAIN ADD for TEXT, ID, and INTEGER Values

The MAINTAIN command with the ADD keyword adds new TEXT, ID, or INTEGER values to a non-concat dimension or composite.

Note: You can also issue this MAINTAIN ADD statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program one time for each value in valuelist; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN composite|dimension ADD valuelist [FIRST|LAST|BEFORE position|AFTER position]

Arguments

dimension

  A non-concat dimension, already defined in an attached analytic workspace.

composite

  A composite. When the composite is a named composite, it must be defined in an attached analytic workspace. When the composite is unnamed, it must have been used in defining an object in an attached analytic workspace. Use the SPARSE keyword to refer to an unnamed composite (for example, `SPARSE <market product>`).

ADD valuelist

  Specifies that the values in valuelist are to be added to the dimension or composite:

  - When you use this argument to add values to a composite or a dimension of type TEXT or ID, the valuelist can be text literals or a TEXT or ID expression. When it is a multiline text expression, each element (line) is treated as a separate value.Do not add null dimension values (empty single quotes) or values that consists of spaces only, because there is no way you can refer to such values in the future.

  - When dimension is INTEGER, valuelist can be an `INTEGER` quantity, such as 5 or 100.

FIRST
LAST

  Specify the logical position at which dimension values will be added. FIRST indicates that the new values will be inserted before any existing values. LAST indicates that new values will be added at the end of the current values. LAST is the default. When you are adding a certain quantity of `INTEGER` values to an INTEGER dimension, that quantity of `INTEGER` values will be added before or at the end of any existing `INTEGER` values (depending on your specification), and all the `INTEGER` values in the resulting series will be automatically adjusted into simple numerical order.

  All values specified before the keyword FIRST or LAST are placed in that position, not just the one value immediately preceding the keyword in your statement.

BEFORE position
AFTER position

  Specify a position before or after which the dimension values are to be added. For position you can specify an existing dimension value, a character expression whose value is an existing dimension value, or an `INTEGER` expression whose value represents the position of a dimension value. When you are adding a certain quantity of `INTEGER` values to an INTEGER dimension, that quantity of `INTEGER` values will be added before or after the `INTEGER` position you specify, and the `INTEGER` values in the whole of the resulting series will be automatically adjusted into simple numerical order.

  All values specified before the keywords BEFORE or AFTER are placed in that position, not just the one value immediately preceding the keyword in your statement.

Notes

Adding Values to an INTEGER Dimension

When you use MAINTAIN to add values in an INTEGER dimension, the values are renumbered to keep the normal sequence of `INTEGER` values (1, 2, 3, ...).

Conjoint Dimensions and Composites

Each value of a conjoint dimension or composite is a combination of values from each of the dimensions (and composites, if any) in its dimension list. To add values to a conjoint dimension or composite, specify each value combination enclosed in angle brackets. The values in a given combination must be in the same order as the dimensions and composites in the definition of the conjoint dimension or composite. Each dimension value in the combination must already exist as a value in the corresponding base dimension. However, when a composite value in the combination does not exist, Oracle OLAP will automatically add the value to the appropriate composite.

Examples

Example 16-38 Adding Values to a TEXT Dimension

This statement adds `Omaha` and `Seattle` to the end of the dimension values for the `city` dimension.

    MAINTAIN city ADD 'Omaha' 'Seattle'

This statement adds `Atlanta` at the beginning of the list of cities and inserts `Peoria` after `Omaha`.

    MAINTAIN city ADD 'Atlanta' FIRST, 'Peoria' AFTER 'Omaha'

Here the value of the TEXT variable `textvar` is inserted before the fifth dimension value of `city`. When you assign the value `Columbus` to `textvar`, you must make sure it is in mixed case, because you want the dimension value to be in mixed case.

    textvar = 'Columbus'
    MAINTAIN city ADD textvar BEFORE 5

Example 16-39 Adding Values to a Conjoint Dimension

The following is an example of adding values to a conjoint dimension.

    DEFINE proddist DIMENSION <product, district>
    MAINTAIN proddist ADD <'Tents' 'Boston'> <'Footwear' 'Denver'>

You can also assign a value of a base dimension to a text variable and use the name of the variable inside the angle brackets.

    prodname = 'Canoes'
    distname = 'Seattle'
    MAINTAIN proddist ADD <prodname, distname>

### MAINTAIN ADD for DAY, WEEK, MONTH, QUARTER, and YEAR Values

The MAINTAIN command with the ADD keyword adds new values to a dimension of type DAY, WEEK, MONTH, QUARTER, and YEAR.

Syntax

MAINTAIN dimension ADD {valuelist|{n PERIODS FIRST}|{n PERIODS LAST}}

Arguments

dimension

  A non-concat dimension, already defined in an attached analytic workspace.

ADD valuelist

  Specifies that the values in valuelist are to be added to the dimension. When dimension is of type DAY, WEEK, MONTH, QUARTER, or YEAR, then valuelist can be text constants or a TEXT, ID, or DATE expression. When the values are TEXT, they can be in the format specified by the VNF (value name format) for the dimension (or in the default format for the type of dimension you are maintaining when the dimension does not have a VNF) or in a valid input style for date values. When the values are specified as a TEXT expression, each element or line is treated as a separate value.

  When the values are in the format specified by the VNF or in the default format for this type of dimension, each value explicitly indicates the time period you want to add. For example, assume that the VNF for a `month` dimension is `'<MTXT><YY>'`. In this case, the value `JAN99` represents the month January 1999.

  When you specify a value for a DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a date, you must provide only the date components that are relevant for the type of dimension you are maintaining. For a DAY or WEEK dimension, you must supply the day, month, and year components. For a MONTH or QUARTER dimension, you must supply only the month and year (for example, `'JUN98'` or `'0698'` for June 1998). For a YEAR dimension, you must specify only the year (for example, `'98'` for 1998). For information about the valid input styles for dates, see DATEORDER.

  When you add a dimension value by specifying a DATE expression or a TEXT value that represents a complete date, you can specify any date that falls within the time period you want to add. For example, to add the month January 1999, you can specify any date from `'01JAN99'` through `'31JAN99`'. Oracle OLAP uses the DATEORDER option to resolve any ambiguities.

  When adding values to a DAY, WEEK, MONTH, QUARTER, or YEAR dimension that does not yet have values, you must specify only the first and last values you want to add for the dimension. Oracle OLAP automatically fills in the gaps with appropriate values for the intervening time periods.

  When a DAY, WEEK, MONTH, QUARTER, or YEAR dimension already has values, you can add values only at the beginning or the end of the existing list. To add values, you must specify only the first or last value you want to add. Oracle OLAP automatically fills in the gap between the existing list and the value you specify.

n PERIODS FIRST
n PERIODS LAST

  Specifies a number of periods to add at the beginning or end of an existing list of dimension values.

Examples

Example 16-40 Adding Values to Dimension of Type QUARTER

In this example you define a new QUARTER dimension, called `qtr`, and you add dimension values for the quarters in 1998 and 1999. You only need to add the first and last dimension values you want. Oracle OLAP fills in the intervening values. To add the first and last quarters, you can specify any dates that fall within those quarters.

    DEFINE qtr DIMENSION QUARTER
    MAINTAIN qtr ADD '01jan98' '31dec99'

### MAINTAIN ADD SESSION

The MAINTAIN command with the ADD SESSION keywords adds a temporary calculated member to a dimension and applies it to the specified objects; or applies a previously-defined calculated member to the specified objects. The calculated member and its definition do not persist from session to session; both are deleted at the end of the session in which they are created.

Syntax

MAINTAIN dimension ADD SESSION member_name [= calculation] -

[STEP DIMENSION (stepdim...)][apply-to]

where:

- calculation is one of the following:

    model-equation
    AGGREGATION (dimension-members....)

- apply-to specifies the basis on which the custom aggregation is added using one of the following phrases:

    APPLY TO AGGMAP aggmaps
    APPLY FOR VARIABLE variables
    APPLY WITH RELATION relations

Arguments

dimension

  A dimension that is already defined in an attached analytic workspace. You can specify any type of dimension for dimension except a non-unique concat dimension or a base dimension of either a unique or non-unique concat dimension.

ADD SESSION

  ADD SESSION indicates maintenance of a temporary calculated member.

member-name

  Specifies the name of the temporary calculated member.

=

  Indicates that you are defining a new calculated member.

model-equation

  A text expression that specifies the calculation used as a dynamic model to calculate custom member values. (See SET for more information about model equations.)

AGGREGATION

  Indicates that the temporary calculated member is added as a custom aggregation using the specified dimension members. This clause effectively modifies the RELATION statement of aggmap objects that are the aggregation specification for variables dimensioned by dimension. Consequently, a MAINTAIN ADD SESSION statement that contains an AGGREGATION clause must also contain an APPLY WITH RELATION clause.

dimension-members

  A text expression that specifies one or more dimension values to be used by the custom aggregation. When using a literal to specify more than one dimension member, separate the values with commas

STEP DIMENSIONS

  Indicates that the calculation is a time-series function (see "Time-Series Functions").

stepdim

  A text expression that specifies the dimension along which the time-series function is calculated. When using a literal to specify more than one dimension name, separate the names with commas.

APPLY TO AGGMAP

  Indicates that the calculated temporary member is added only to dimensions used by the specified aggmap objects.

aggmaps

  A text expression that specifies the name of one or more aggmap objects to which the temporary calculated member is added. When using a literal to specify more than one aggmap object, separate the names with commas. The temporary calculated member is added to each of the specified aggmap objects.

APPLY FOR VARIABLE

  Indicates that the temporary calculated member is added only to dimensions used by the default aggmap objects for the specified variables.

variables

  A text expression that specifies the one or more variable names for which the temporary calculated member is added to. When using a literal to specify more than one variable name, separate the names with commas. The temporary calculated member is added to the default aggmap object of each specified variable.

  Important: When a specified variable does not have a default aggmap, using this clause generates an error. Use AGGMAP SET or $AGGMAP to specify a default aggmap for the variable.

APPLY WITH RELATION

  Indicates that the temporary calculated member is added dimensions used by the aggregation specifications that contain a RELATION statement for the specified relation.

relation

  A text expression that specifies the name of the relation for which there must be a RELATION statement in the AGGMAP statement.

Notes

Finding Out Information About Temporary Calculated Members

Once you have added a temporary calculated member using a MAINTAIN statement, you can use AGGMAPINFO to discover the temporary calculated members you have added, the equations used to calculate members, and the dimension members used in the right-hand side of equations used to calculate custom members.

Examples

Example 16-41 Creating Calculated Dimension Members with Aggregated Values

Assume that an analytic workspace has a dimension named `letter` and a variable named `my_quantity` with the following definitions and permanent values.

    DEFINE letter DIMENSION TEXT
    DEFINE my_quantity VARIABLE DECIMAL <letter>

    LETTER                  MY_QUANTITY
    -------------- ------------------------------
    A                                       10.00
    B                                      100.00

You can define temporary dimension members for the `letter` dimension and aggregate data in `my_quantity` for those members following these steps:

1. Determine the aggregation that you want to perform and define and populate the necessary supporting objects.

   1. Create an empty child-parent relation for the `letter` dimension

          DEFINE letter.parentrel RELATION letter <letter>

          LETTER                LETTER.PARENTREL
          -------------- ------------------------------
          A              NA
          B              NA

   2. Define a simple model to be used to calculate values associated with the `letter` dimension

          DEFINE my_model MODEL
          MODEL
            DIMENSION letter
           END

   3. Define and compile a simple aggmap to be used to calculate `my_quantity` values associated with the `letter` dimension

          DEFINE my_aggmap AGGMAP
          AGGMAP
             RELATION letter.parentrel PRECOMPUTE(NA)
             MODEL my_model PRECOMPUTE(NA)
            END

          COMPILE my_aggmap

   4. Define a variable to contain the definition for the custom aggregation, This new variable will be the same as `my_quantity` except that has `my_aggmap` as its default aggmap.

          DEFINE my_quantity_definition VARIABLE DECIMAL <letter>

          CONSIDER my_quantity_definition
          PROPERTY '$AGGMAP' 'my_aggmap'

          REPORT my_quantity_definition

          LETTER             MY_QUANTITY_DEFINITION
          -------------- ------------------------------
          A                                          NA
          B                                          NA

2. Add temporary members to the `letter` dimension and specify how variable values for those members are to be calculated.

       MAINTAIN letter ADD SESSION 'C' = 'A' * 'B'
       MAINTAIN letter ADD SESSION 'D' = AGGREGATION('A', 'B') -
           APPLY TO AGGMAP my_aggmap
       MAINTAIN letter ADD SESSION 'E' = 'C' + 'D' -
           APPLY WITH RELATION letter.parentrel
       MAINTAIN letter ADD SESSION 'F' = 10 * 'E' -
           APPLY FOR VARIABLE my_quantity_definition

   A report of the `letter` dimension shows the new dimension members.

       LETTER
       --------------
       A
       B
       C
       D
       E
       F

3. Aggregate `my_quantity` using the aggmap object named `my_aggmap`.

       REPORT AGGREGATE(my_quantity USING my_aggmap)

                       AGGREGATE(MY_QUANTITY USING
       LETTER                   MY_AGGMAP)
       -------------- ------------------------------
       A                                       10.00
       B                                      100.00
       C                                    1,000.00
       D                                      110.00
       E                                    1,110.00
       F                                   11,100.00

Assume now that you issue the UPDATE and COMMIT statements to update and commit your analytic workspace. Then you detach the analytic workspace and end your session.

Later you start a new session and attach the same analytic workspace. When you ask for a description of the analytic workspace you can see that all of the objects that were in the analytic workspace when the UPDATE was issued still exist.

    DEFINE LETTER DIMENSION TEXT

    DEFINE LETTER.PARENTREL RELATION LETTER <LETTER>

    DEFINE MY_QUANTITY VARIABLE DECIMAL <LETTER>

    DEFINE MY_MODEL MODEL
    MODEL
    DIMENSION letter
    END

    DEFINE MY_AGGMAP AGGMAP
    AGGMAP
    RELATION letter.parentrel PRECOMPUTE(NA)
    MODEL my_model PRECOMPUTE(NA)
    END

    DEFINE MY_QUANTITY_DEFINITION VARIABLE DECIMAL <LETTER>

However, when you report on the `letter` dimension and the `my_quantity` variable, the temporary dimension members that you added in the previous session and their related values in the `my_quantity` variable do not exist.

    LETTER
    --------------
    A
    B

    REPORT letter.parentrel

    LETTER                LETTER.PARENTREL
    -------------- ------------------------------
    A              NA
    B              NA

    REPORT my_quantity

    LETTER                  MY_QUANTITY
    -------------- ------------------------------
    A                                       10.00
    B                                      100.00

    LETTER             MY_QUANTITY_DEFINITION
    -------------- ------------------------------
    A                                          NA
    B                                          NA

    REPORT AGGREGATE(my_quantity USING my_aggmap)

                    AGGREGATE(MY_QUANTITY USING
    LETTER                   MY_AGGMAP)
    -------------- ------------------------------
    A                                       10.00
    B                                      100.00

### MAINTAIN ADD TO PARTITION

The MAINTAIN ADD TO PARTITION statement adds previously-populated dimension or composite values to a partition of a previously-defined partition template object.

Tip: Use MAINTAIN MOVE TO PARTITION to maintain partition values when you have already populated a partitioned variable.

Syntax

MAINTAIN partition-template ADD TO PARTITION partition valuelist

Arguments

partition-template

  A text expression that is the name of a previously-defined partition template object.

ADD TO PARTITION

  Specifies that values are to be added to the partition.

partition

  A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.

valuelist

  Text literals or a TEXT or ID expression specifying the values to be added. When it is a TEXT expression, each element (line) is treated as a separate value. The values in the expression are added exactly as they are typed.

  For a concat dimension, you can specify a value of the concat dimension, or the name of a component dimension and a value or position of that dimension. You can use the values of a dimension surrogate as the values of value.

  Note that you cannot partition along an `INTEGER` dimension.

TO

  Indicates a range of values.

Examples

For an example of adding values to a partition, see Example 16-47, "Adding and Deleting Partition Values".

## MAINTAIN DELETE

The MAINTAIN command with the DELETE keyword deletes members from non-concat dimensions and composites; or deletes the data of previously-partitioned variables from one partition to another as it changes the dimension or composite values defined for a partition in the partition template which the variables are dimensioned.

Note: You can also issue a MAINTAIN DELETE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

The syntax for using the DELETE keyword of the MAINTAIN command to delete members varies depending on the type of object from which you are deleting the members. For this reason, the following separate entries are provided for MAINTAIN DELETE:

- MAINTAIN DELETE dimension

- MAINTAIN DELETE composite

- MAINTAIN DELETE FROM PARTITION

### MAINTAIN DELETE dimension

The MAINTAIN command with the DELETE keyword deletes dimension members from non-concat dimensions.

Note: You can also issue a MAINTAIN DELETE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

See also: MAINTAIN DELETE composite

Syntax

MAINTAIN dimension DELETE dim-arg

Arguments

dimension

  A non-concat dimension, already defined in an attached analytic workspace, whose values are to be deleted.

  Note: You cannot use a dimension surrogate as the dimension argument of a MAINTAIN DELETE command. However, you can use a dimension surrogate as a value within the dim-arg parameter.

dim-arg

  Specifies the values to be deleted from the values of a dimension. You can specify these values using the same syntax that you use for limit-clause in the LIMIT command.

  See: There are several types of limit clauses. Because the complete syntax for each type of limit clause is complex, there are individual LIMIT entries for each type of clause: LIMIT command (using values) LIMIT command (using LEVELREL) LIMIT command (using related dimension) LIMIT command (using parent relation) LIMIT command (NOCONVERT) LIMIT command (using POSLIST)

Notes

Deleting Temporary Calculated Members From Dimensions

When you use a MAINTAIN DELETE statement to delete a temporary calculated member, Oracle OLAP:

1. Deletes the member from the dimension.

2. Removes the calculation from all aggmap objects that currently contain the corresponding calculation.

Deleting Values in an Integer Dimension

When you use MAINTAIN to delete values in an INTEGER dimension, the values are renumbered to keep the normal sequence of `INTEGER` values (1, 2, 3, ...).

Examples

Example 16-42 Deleting Dimension Values by Value

This statement deletes `Omaha` and `Newark` from the values for `city`.

    MAINTAIN city DELETE 'Omaha' 'Newark'

Example 16-43 Deleting the First Five Values of a Dimension

In this example, you use the INTEGER variable `intvar` to remove the first five cities from the dimension `city`.

    intvar = 5
    MAINTAIN city DELETE FIRST intvar

Example 16-44 Deleting Dimension Values Based on a Boolean Expression

Here you remove from `city` all those cities with a population of less than 75,000 people. You use the variable `population.c`, which contains the population for each city.

    MAINTAIN city DELETE population.c LT 75000

Example 16-45 Deleting Dimension Values Using Surrogate to Specify Values

Assume that `prodid` is a `NUMBER` dimension and `prodtype` is a `TEXT` dimension surrogate for `prodid`. Assume also that the values of `prodid` are `17`, `40`, and `56`. The values of `prodtype` are `Two-Person Tent`, `Three-person Tent`, and `Four-person Tent`. The following statement deletes a value from `prodid` and from its surrogate.

    MAINTAIN prodid DELETE prodid(prodtype 'Three-Person Tent')

Example 16-46 Deleting Related MONTH Values

In this example, you use the related dimension `quarter` to remove values of the dimension `month`. All months related to the values of `quarter` currently in the status are deleted.

    LIMIT quarter TO FIRST 1

MAINTAIN month DELETE quarter

### MAINTAIN DELETE composite

The MAINTAIN command with the DELETE keyword deletes dimension members from composites.

See also: MAINTAIN DELETE dimension

Syntax

MAINTAIN composite DELETE comp-arg

where comp-arg is one of the following constructs:

  valuelist
  ALL
  base-dim [valuelist]
  boolean-expression
  {TOP | BOTTOM} n BASEDON exp
  {TOP | BOTTOM} n-percent PERCENTOF expression
  LONGLIST

Arguments

composite

  A composite whose values are to be deleted. When the composite is a named composite, it must be defined in an attached analytic workspace. When the composite is unnamed, it must have been used in defining an object in an attached analytic workspace.

  Use the SPARSE keyword to refer to an unnamed composite (for example, `SPARSE <market product>`).

valuelist

  Specifies one or more values to be deleted from the composite. The valuelist can be text constants or a text expression.

ALL

  Deletes all composite values. This does not delete the definition of the composite itself.

base-dim [valuelist]

  Deletes the values that include the listed values of a base dimension of the composite. The argument valuelist can be one value, a list of values, or a range of values (using TO to specify an inclusive range). You cannot use position numbers to specify a range of values. When you omit valuelist, Oracle OLAP deletes all values that include base-dim values currently in status.

boolean-expression

  Deletes all composite values for which the Boolean expression is `TRUE`. The boolean-expression must be dimensioned by the dimension or the composite from which you the values deleted. When it has additional dimensions, their status must each be limited to one value.

TOP n BASEDON exp
BOTTOM n BASEDON exp

  Deletes the top or bottom n values based on the highest (TOP) or lowest (BOTTOM) values in exp. The expression must be dimensioned by the composite from which you the values deleted. When it has additional dimensions, their status must each be limited to one value.

BOTTOM n-percent PERCENTOF expression
TOP n-percent PERCENTOF expression

  Deletes values by finding the top or bottom performers based on a criterion. This construction sorts values and deletes them based on their contribution, by percentage, to an expression.

LONGLIST

  Indicates a long list (up to 2,000 values) of individual values to delete. When there are fewer than 300 values, LONGLIST is not needed.

### MAINTAIN DELETE FROM PARTITION

The MAINTAIN DELETE FROM PARTITION command deletes the data of previously-partitioned variables from one partition to another as it changes the dimension or composite values defined for a partition in the partition template which the variables are dimensioned.

Tip: Use MAINTAIN MOVE TO PARTITION to maintain partition values when you have already populated a partitioned variable.

Syntax

MAINTAIN partition-template DELETE FROM PARTITION partition { dim-arg| comp-arg}

Arguments

partition-template

  A text expression that is the name of a previously-defined partition template object.

partition

  A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.

DELETE FROM PARTITION

  Specifies that values are to be deleted from the partition and from partitioned variables dimensioned using a partition template that includes the partition.

dim-args

  Specifies the values of a dimension that to use when deleting partitioned variable values and when redefining the values that are in the partition You can use any of the constructs specified for the dim-arg argument in MAINTAIN DELETE dimension.

comp--args

  Specifies the values of a composite to use when deleting partitioned variable values and when redefining the values that are in the partition You can use any of the constructs specified for the comp-arg argument in MAINTAIN DELETE composite.

Examples

Example 16-47 Adding and Deleting Partition Values

Assume that you have defined the following objects in your analytic workspace. on

    DEFINE time DIMENSION TEXT
    DEFINE time_parentrel RELATION time <time>
    DEFINE product DIMENSION TEXT
    DEFINE partition_sales_by_year PARTITION TEMPLATE <time product> -
       PARTITION BY LIST (time) -
        (PARTITION time_2004 VALUES ('2004', 'Dec2004', 'Jan2004', '31Dec2004', -
              '01Dec2004', '31Jan2004', '01Jan2004') <TIME PRODUCT> -
         PARTITION time_2003 VALUES ('2003', 'Dec2003', 'Jan2003', '31Dec2003', -
             '01Dec2003', '31Jan2003', '01Jan2003') <TIME PRODUCT> -
         PARTITION time_2002 VALUES ('2002', 'Dec2002', 'Jan2002', '31Dec2002', -
             '01Dec2002', '31Jan2002', '01Jan2002') <TIME PRODUCT>)

Assume that instead of having all sales values dimensioned levels by all time values of a year in a partition, you want to have partitions by days and by summary time values (month and year). To change `partition_sales_by_year` to reflect this new partitioning scheme, you issue the following statements.

    "Create the new partition
    CHGDFN partition_sales_by_year DEFINE -
             (PARTITION partition_month_years VALUES () <time product>)
    "Delete the values for months and years from the partitions for years
    MAINTAIN partition_sales_by_year DELETE FROM PARTITION time_2004 '2004'-
            'Dec2004' 'Jan2004'
    MAINTAIN partition_sales_by_year DELETE FROM PARTITION time_2003 '2003'-
            'Dec2003''Jan2003'
    MAINTAIN partition_sales_by_year DELETE FROM PARTITION time_2002 '2002'-
           'Dec2002' 'Jan2002'
    "Add the month and year values to the new partition for summary values
    MAINTAIN partition_sales_by_year ADD TO PARTITION partition_month_years '2004'-
          'Dec2004' 'Jan2004' '2003' 'Dec2003''Jan2003' '2002' 'Dec2002' 'Jan2002'

The `partition_sales_by_year` partition template object now has the following definition.

    DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
       PARTITION BY LIST (TIME) -
        (PARTITION TIME_2004 VALUES ('31Dec2004', '01Dec2004', '31Jan2004', -
           '01Jan2004') <TIME PRODUCT> -
         PARTITION TIME_2003 VALUES ('31Dec2003', '01Dec2003', '31Jan2003', -
           '01Jan2003') <TIME PRODUCT> -
         PARTITION TIME_2002 VALUES ('31Dec2002', '01Dec2002', '31Jan2002', -
           '01Jan2002') <TIME PRODUCT> -
         PARTITION PARTITION_MONTH_YEARS VALUES ('2004', 'Dec2004', 'Jan2004', -
           '2003', 'Dec2003', 'Jan2003', '2002', 'Dec2002', 'Jan2002')-
             <TIME PRODUCT>)

## MAINTAIN MERGE

The MAINTAIN command with the MERGE keyword provides a quick way to make sure all dimension or composite values on a separate list are included in a non-concat dimension or composite. Using the MERGE keyword with the MAINTAIN command automatically adds the new values from the list and ignores the duplicates. This method of entering dimension values can save a significant amount of time when you have a large number of values to enter.

You can use MERGE with dimensions of any data type, including DAY, WEEK, MONTH, QUARTER, and YEAR dimensions. However, since Oracle OLAP provides a quick way of adding values of DAY, WEEK, MONTH, QUARTER, and YEAR dimensions through the ADD keyword, the MERGE keyword may not be as useful with DAY, WEEK, MONTH, QUARTER, and YEAR dimensions as it is with TEXT or ID dimensions.

At the same time as you are merging values into a dimension, you can also update a relation that involves that dimension.

Note: You can also issue this MAINTAIN MERGE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program one time for each value in exp; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN dimension|composite MERGE exp [RELATE relation]

dimension

  A non-concat dimension, already defined in an attached analytic workspace, whose values are to be entered or changed.

composite

  A composite whose values are to be added, deleted, or merged. When the composite is a named composite, it must be defined in an attached analytic workspace. When the composite is unnamed, it must have been used in defining an object in an attached analytic workspace. Use the SPARSE keyword to refer to an unnamed composite (for example, `SPARSE <market product>`).

exp

  Specifies an expression whose values are to be merged with dimension; for example, the name of a dimensioned text variable that contains dimension values, or a single-cell text variable whose value is a multiline list of dimension values. MAINTAIN MERGE ignores any NAs in exp. When dimension is an INTEGER dimension, then exp specifies the number of values that you want in the dimension. When the actual total is less, MAINTAIN MERGE adds enough values to reach the specified total. For example, when an INTEGER dimension has 10 positions, `MERGE` `5` has no effect; but `MERGE` `15` would add 5 values.

RELATE relation

  Specifies a relation to be updated as new values from exp are merged into dimension. At least one of the dimensions of exp must also appear in the definition of relation. When exp is a single-cell value, you cannot use the RELATE phrase.

Examples

Example 16-48 Using the MERGE Keyword with Composites

Suppose you want to define a composite that is made up of all combinations of the first three values of the `product` dimension and the first five values of the `district` dimension. You can efficiently include all 15 values with the following statements.

    DEFINE comp_proddist COMPOSITE <product district>
    LIMIT product TO FIRST 3
    LIMIT district TO FIRST 5
    MAINTAIN comp_proddist MERGE <product district>

This method works with conjoint dimensions as well.

## MAINTAIN MOVE

A MAINTAIN command with the MOVE keyword has different effects depending on the object on which it operates:

- When maintaining a dimension, MAINTAIN MOVE changes the position of one or more values in a non-concat dimension or a dimension of type TEXT, ID, or INTEGER or adds previously-populated dimension or composite values to a partition

- When maintaining a partition, MAINTAIN MOVE moves the data of a previously-partitioned variables from one partition to another as it changes the dimension or composite values defined for a partition in the partition template which the variables are dimensioned.

Note: You can also issue a MAINTAIN MOVE dimension value statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

The syntax for using the MAINTAIN command with the MOVE keyword depends on the type of the object being maintained.

For this reason, the following separate entries are provided for MAINTAIN MOVE:

- MAINTAIN MOVE dimension value

- MAINTAIN MOVE TO PARTITION

### MAINTAIN MOVE dimension value

A simple MAINTAIN MOVE statement changes the position of one or more values in a non-concat dimension or a dimension of type TEXT, ID, or INTEGER. You cannot use the MOVE keyword of the MAINTAIN command with composites or with dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR.

Note: You can also issue a MAINTAIN MOVE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN dimension MOVE dim-arg

Arguments

dimension

  A non-concat dimension, already defined in an attached analytic workspace, whose values are to be entered or changed. The dimension must be of type TEXT, ID, or INTEGER. You cannot specify a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.

dim-arg

  Specifies the values to be moved. You can specify these values using the same syntax that you use for limit-clause in the LIMIT command.

  See: There are several types of limit clauses. Because the complete syntax for each type of limit clause is complex, there are individual LIMIT entries for each type of clause: LIMIT command (using values) LIMIT command (using LEVELREL) LIMIT command (using related dimension) LIMIT command (using parent relation) LIMIT command (NOCONVERT) LIMIT command (using POSLIST)

Notes

Sorting Values

You can sort the values of a dimension with the following statements.

    LIMIT dimension TO ALL
    SORT dimension A sort-criterion
    MAINTAIN dimension MOVE VALUES(dimension) FIRST

The sorting criterion can be any expression you choose (see the SORT command command). To sort the dimension alphabetically, use the dimension itself as the criterion (see Example 16-51, "Moving Dimension Values into Sorted Order". After using the SORT command command to sort the dimension values, you use the MAINTAIN command to make the sorted order permanent.

You can use the SORT command command for a temporary sort of the values of a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR. For example, you might want to use the sorted order in a report. However, you cannot use the MAINTAIN command to save the sorted order as the permanent order of a dimension with the type of DAY, WEEK, MONTH, QUARTER, or YEAR. The values of these types of dimensions must be stored in increasing chronological order.

Examples

Example 16-49 Moving a Dimension Value to a Specific Position

This statement moves the position of the city `Houston` to the position following the fifth dimension value.

    MAINTAIN city MOVE 'Houston' AFTER 5

Example 16-50 Moving a Dimension Value to the End of the Status List

In this example, you use the TEXT variable `textvar` to move `Seattle` to the end of the list of cities.

    textvar = 'Seattle'
    MAINTAIN city MOVE textvar LAST

Example 16-51 Moving Dimension Values into Sorted Order

Here you put the values of `city` in alphabetical order.

    SORT city A city
    MAINTAIN city MOVE VALUES(city) FIRST

Example 16-52 Moving Values of Concat Dimensions

The following statement moves the `reg.dist.ccdim` concat dimension value `<district: 'Denver'>` after the concat dimension value `<region: 'West'>`.

    MAINTAIN reg.dist.ccdim MOVE <district: 'Denver'> AFTER <region: 'West'>

The following statement moves the concat dimension value `<district: 'Denver'>` after the position that corresponds to the first value of the component `district` dimension. If the first value in the status of `district` is `Atlanta`, then `<district: 'Denver'>` moves after the value `<district: 'Atlanta'>` in the concat dimension.

    MAINTAIN reg.dist.ccdim MOVE <district: 'Denver'> AFTER <district: 1>

The following statement moves the concat dimension value `<district: 'Dallas'>` after the third value of the concat dimension.

    MAINTAIN reg.dist.ccdim MOVE <district: 'Dallas'> AFTER 3

### MAINTAIN MOVE TO PARTITION

A MAINTAIN MOVE TO PARTITION statement combines both add and move capabilities. You can use a MAINTAIN MOVE TO PARTITION statement to:

- Add previously-populated dimension or composite values to a partition in the same manner as MAINTAIN ADD TO PARTITION

- Change the dimension or composite values defined for a partition in the partition template by which the variables are dimensioned and, at the same time, move the data of a previously-partitioned variables dimensioned by those dimensions and composites from one partition to another.

Syntax

MAINTAIN partition-template MOVE TO PARTITION partition value [TO value]

Arguments

partition-template

  A text expression that is the name of a previously-defined partition template object.

MOVE TO PARTITION

  Specifies that values are to be added to the partition or moved from one partition to another.

partition

  A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.

value

  Specifies one or more values of a previously-populated dimension or composite. You can specify these values as:

  - A literal value.

  - An expression whose value is a dimension value.

  - For all dimensions except `NUMBER` dimensions, an `INTEGER` expression whose value represents the position of a dimension value.

  - A valueset.

  For a concat dimension, you can specify a value of the concat dimension, or the name of a component dimension and a value or position of that dimension. You can use the values of a dimension surrogate as the values of value.

TO

  Indicates a range of values.

Examples

Example 16-53 Specifying the Values of a Partition Using Valuesets

Assume that you have defined a partition template object with the following definition that does not specify the actual dimension values for each partition.

    DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
       PARTITION BY LIST (TIME) -
        (PARTITION TIME_2004 VALUES () <TIME PRODUCT> -
         PARTITION TIME_2003 VALUES () <TIME PRODUCT> -
         PARTITION TIME_2002 VALUES () <TIME PRODUCT>)

To specify the values of each partition using valuesets, you take the following steps:

1. Define a valueset for each year's values.

       DEFINE vs_2004 VALUESET time
       LIMIT vs_2004 to '01Dec2004' '31Dec2004' '01Jan2004''31Jan2004' -
            'Jan2004' 'Dec2004' '2004'
       DEFINE vs_2003 VALUESET time
       LIMIT vs_2003 to '01Dec2003' '31Dec2003' '01Jan2003''31Jan2003' -
            'Jan2003' 'Dec2003' '2003'
       DEFINE vs_2002 VALUESET time
       LIMIT vs_2002 to '01Dec2002' '31Dec2002' '01Jan2002''31Jan2002' -
            'Jan2002' 'Dec2002' '2002'

2. Using MAINTAIN MOVE statements, specify values for the partitions of the partition template.

       MAINTAIN partition_sales_by_year MOVE TO PARTITION time_2004 vs_2004
       MAINTAIN partition_sales_by_year MOVE TO PARTITION time_2003 vs_2003
       MAINTAIN partition_sales_by_year MOVE TO PARTITION time_2002 vs_2002

When you issue a DESCRIBE statement, you can see that the description of the `partition_sales_by_year` partition template now includes the appropriate values of `time` in each partition definition.

    DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
        PARTITION BY LIST (TIME) -
        (PARTITION TIME_2004 VALUES -
    ('2004','Dec2004','Jan2004', 31Dec2004',01Dec2004','31Jan2004','01Jan2004')-
         PARTITION TIME_2003 VALUES -
    ('2003','Dec2003','Jan2003', 31Dec2003',01Dec2003','31Jan2003','01Jan2003')-
          PARTITION TIME_2002 VALUES -
                         ('2002','Dec2002','Jan2002', 31Dec2002',01Dec2002','31Jan2002','01Jan2002'))

## MAINTAIN RENAME

The MAINTAIN command with the RENAME keyword changes the spelling of one or more dimension values. You cannot use RENAME keyword with a composite or with dimensions of type INTEGER, DAY, WEEK, MONTH, QUARTER, or YEAR.

Note: You can also issue a MAINTAIN RENAME statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN dimension RENAME {value new-value}...

Arguments

dimension

  A non-concat dimension of type TEXT or ID that is already defined in an attached analytic workspace and whose values are to be renamed. You cannot specify a dimension of type INTEGER, DAY, WEEK, MONTH, QUARTER, or YEAR.

value

  Specifies an existing dimension value to be renamed. You can specify a dimension value, a character expression whose value is a dimension value, or an `INTEGER` expression whose value represents the position of a dimension value.

new-value

  A text constant or a TEXT or ID expression that is the new spelling for the dimension value.

Examples

Example 16-54 Renaming Values of a TEXT Dimension

This statement changes the spelling of the cities `Chic` and `Bost` to `Chicago` and `Boston`.

    MAINTAIN city RENAME 'Chic' 'Chicago' 'Bost' 'Boston'

In this example you use the TEXT variable `textvar` to change the second city to `Atlanta`.

    textvar = 'Atlanta'
    MAINTAIN city RENAME 2 textvar